﻿using BusinessRepository;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using BusinessEntity;
using BusinessEntity.CGGLQuerry;
using BusinessEntity.CGGL;
using BusinessRule.Organization;
using APICommond;

namespace BusinessRule
{
    public class CGGL
    {
        //获取项目用户 depid
        public static async Task<string> GetUserDepid(string userid)
        {
            string str2 = "(";

            DataTable table = await TranstionRule.ProcessSql("select ksid from view_userinfos where userid ='" + userid + "'");
            for (int i = 0; i < table.Rows.Count; i++)
            {
                string[] strArray = table.Rows[i]["ksid"].ToString().Split(new char[] { ',' });
                for (int j = 0; j < strArray.Length; j++)
                {
                    str2 = str2 + "'" + strArray[j].ToString() + "',";
                }
                str2 = str2.TrimEnd(new char[] { ',' });
            }
            return (str2 + ")").Replace("()", "('')");
        }
        #region 字典
        //审核状态
        public static async Task<DataTable> GetSHZT()
        {
            string sql = @"select ztdm,ztmc from CG_SHZT where type='CG'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //招标方式
        public static async Task<DataTable> GetZBFS()
        {
            string sql = @"select ztdm zbfsdm,ztmc zbfsmc from CG_SHZT where type='ZBFS'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        #endregion
        #region 采购计划
        //根据查询条件反馈列表
        //public static async Task<(DataTable, int)> GetCGJH(CGJHQuerry querry)
        public static async Task<(IEnumerable<dynamic>, int)> GetCGJH(CGJHQuerry querry,string strdepids)
        {
            string str = "";
            
            //查询条件--计划名称
            if (!(string.IsNullOrEmpty(querry.jhmc)))
            {
                str += " and jhmc like '%" + querry.jhmc + "%'";
            }
            //查询条件--部门
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--状态
            if (!(string.IsNullOrEmpty(querry.zt)))
            {
                str += " and zt in('" + querry.zt + "')";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            string sql = "select * from CG_CGJH where  bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            //return await new RepositoryFactory().BaseRepository().FindTable(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
        //查询单个采购计划
        public static async Task<DataTable> GetCGJHByID( string listid)
        {
            string sql = @"select * from CG_CGJH where  listid='" + listid + "'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<dynamic> GetCGJHEByID(string listid)
        {
            string sql = @"select * from CG_CGJH where  listid='" + listid + "'";
            return await new RepositoryFactory().BaseRepository().FindEntity<CG_CGJH>(sql, null);
        }
        public static async Task InsertCG_JH(CG_CGJH cgjh)
        {
            await new RepositoryFactory().BaseRepository().Insert(cgjh);
        }
        public static async Task UpdateCG_JH(CG_CGJH cgjh)
        {
            await new RepositoryFactory().BaseRepository().Update(cgjh);

        }
        //提交采购计划
        public static async Task SubmitCGJH(string listid)
        {
            string sql = @"update CG_CGJH set zt='1' where  listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        
        public static async Task DeleteCGJH(string listid)
        {
            string sql = @"delete from CG_CGJH where  listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }

        #endregion
        #region 采购项目
        //根据查询条件反馈列表
        //public static async Task<(DataTable,int)> GetCGXM(CGXMQuerry querry)

        public static async Task<(IEnumerable<dynamic>, int)> GetCGXM(CGXMQuerry querry,string strdepids)
        {
            string str = "";
            //查询条件--采购项目名称
            if (!(string.IsNullOrEmpty(querry.xmmc)))
            {
                str += " and xmmc like '%" + querry.xmmc + "%'";
            }
            //查询条件--采购方式
            if (!(string.IsNullOrEmpty(querry.cgfs)))
            {
                str += " and cgfs in('" + querry.cgfs + "')";
            }
            //查询条件--部门
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--状态
            if (!(string.IsNullOrEmpty(querry.zt)))
            {
                str += " and zt in('" + querry.zt + "')";
            }
            //查询条件--查询开始时间
            if (querry.Begindate!=null)
            {
                str += " and lrrq >= '" + querry.Begindate + "'";
            }
            //查询条件--查询结束时间
            if (querry.Enddate != null)
            {
                str += " and lrrq <= '" + querry.Enddate + "'";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            string sql = "select * from CG_CGXMML where bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
        //查询单个采购项目
        public static async Task<DataTable> GetCGXMByID(string listid)
        {
            string sql = @"select * from CG_CGXMML where listid='" + listid + "';";//目录
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个采购项目的明细
        public static async Task<DataTable> GetCGXMMXByID(string listid)
        {
            string sql = "select * from CG_CGXMMX where xmid='" + listid + "' order by listid;";  //明细
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个采购项目的采购计划
        public static async Task<DataTable> GetCGXMJHByID(string listid)
        {
            string sql = @"select listid,xmid,a.jhid,jhmc,jhje,b.bcsyje yyje,jhje-b.bcsyje kyje,cgpm, wpmc, ggxh, jldw, wpdj, wpsl, ysje je, bz,a.bcsyje from 
(select a.listid,xmid,jhid, jhmc, jhje, cgpm, wpmc, ggxh, jldw, wpdj, wpsl, ysje,bcsyje, bz  from CG_CGXM_JH a, CG_CGJH b where  xmid = '" + listid + "' and a.jhid = b.listid)a "+
"left join "+
"(select jhid, sum(bcsyje)bcsyje from CG_CGXM_JH where listid not in (select listid from CG_CGXM_JH where xmid = '" + listid + "') group by jhid)b "+
"on a.jhid = b.jhid order by listid; ";  //计划明细
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个采购项目的指标信息
        public static async Task<DataTable> GetCGXMZBByID(string listid)
        {
            string sql = @"select listid,xmid,a.zbid,''zbzy,a.zy,isnull(e.je,0)zbje,bcsyje,isnull(e.je,0)-isnull(d.je,0)-isnull(SQJHJE,0)-isnull(PFJHJE,0) syje,zcsx,dw,xm,jflx,zbly,zjly,gnkm,jjfl from 
(select * from CG_CGXMZB where listid='"+ listid + "')a "+
"left join "+
"(select * from ZB_view_MXZB where ZBZT<>'2'   and MXZBLB='0'   and shr_id<>-1 )e on a.zbid=e.zbid "+
"left join "+
"(select ZBID,sum(case when (b.PFFZR_ID=-1 ) then SQJE else 0 end) SQJHJE,sum(case when (b.PFFZR_ID<>-1) then JE else 0 end) PFJHJE from ZB_YKJHML  a, ZB_YKJHNR  b  where a.gsdm=b.gsdm "+
"and a.kjnd=b.kjnd    and a.jhbh=b.jhbh    and a.jhlb=b.jhlb    and a.sfzbkz=1 "+
"and ((b.PFFZR_ID=-1 and SQJE>0) or (PFFZR_ID<>-1))   and (a.zt=0 or a.zt=1)  group by zbid)c on a.ZBID=c.zbid "+
"left join "+
"(select ZBID, Sum(isnull(je, 0))JE from zb_view_zlzml Where  (zlzLB = '0' or zlzLB = '1') and zbkzbz = '1' and zt = 0 group by zbid)d on a.ZBID = d.zbid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询采购计划列表
        public static async Task<(DataTable,int)> GetCGJHLB(string kjnd,int Size, int Current, string sortString, string sort, string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            string sql = @"select listid jhid,jhmc,jhje,isnull(bcsyje,0) yyje,isnull(jhje,0)-isnull(bcsyje,0) kyje,cgpm, wpmc, ggxh, jldw, wpdj, wpsl, ysje je, isnull(bz,'')bz from " +
"(select b.listid, jhmc, jhje, cgpm, wpmc, ggxh, jldw, wpdj, wpsl, ysje, bz,bcsyje  from CG_CGJH b left join  (select jhid,sum(bcsyje)bcsyje from CG_CGXM_JH where 1=1 " + str + " group by jhid) a on bmdm in " + strdepids + "  and a.jhid = b.listid" + str+")a ";
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }
                    
            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "jhid";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        public static async Task<DataTable> GetCGJHLBbyids(string uid, string [] listids)
        {
            string listid = "";
            for (int i = 0; i < listids.Length; i++)
            {
                listid += "'" + listids[i].ToString() + "',";

            }
            listid = listid.Substring(0, listid.LastIndexOf(","));
            string sql = @"select listid jhid,jhmc,jhje,isnull(bcsyje,0) yyje,isnull(jhje,0)-isnull(bcsyje,0) kyje,cgpm, wpmc, ggxh, jldw, wpdj, wpsl, ysje je, isnull(bz,'')bz from " +
"(select b.listid, jhmc, jhje, cgpm, wpmc, ggxh, jldw, wpdj, wpsl, ysje, bz,bcsyje  from CG_CGJH b left join (select jhid,sum(bcsyje)bcsyje from CG_CGXM_JH where  jhid in(" + listid + ") group by jhid) a on bmdm in " + await GetUserDepid(uid) + "  and a.jhid = b.listid where b.listid in(" + listid + "))a order by listid";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询指标列表
        public static async Task<(DataTable,int)> GetCGZBLB(string bmdm, string kjnd, int Size, int Current, string sortString, string sort, string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            if (string.IsNullOrEmpty(bmdm))
            {
            }
            else
            {
                if (bmdm != "")
                {
                    str += " and ysdwdm in('" + bmdm + "')";
                }
            }
            string sql = @"select a.zbid,''zbzy,a.zy,isnull(a.je,0)zbje,(isnull(d.je,0)+isnull(SQJHJE,0)+isnull(PFJHJE,0)+isnull(e.je,0))yyje,isnull(a.je,0)-isnull(d.je,0)-isnull(SQJHJE,0)-isnull(PFJHJE,0)-isnull(e.je,0) kyje,''zcsx,ysdwdm+':'+ysdwmc dw,xmdm+':'+xmmc xm,zclxdm+':'+zclxmc jflx,zblydm+':'+zblymc zbly,zjxzdm+':'+zjxzmc zjly,yskmdm+':'+yskmmc gnkm,zfjflxdm+':'+zfjflxmc jjfl from 

(select * from ZB_view_MXZB where  ZBZT<>'2'   and MXZBLB='0'   and shr_id<>-1 "+str+")a "+
"left join "+
"(select ZBID,sum(case when (b.PFFZR_ID=-1 ) then SQJE else 0 end) SQJHJE,sum(case when (b.PFFZR_ID<>-1) then JE else 0 end) PFJHJE from ZB_YKJHML  a, ZB_YKJHNR  b  where a.gsdm=b.gsdm  " +
"and a.kjnd=b.kjnd    and a.jhbh=b.jhbh    and a.jhlb=b.jhlb    and a.sfzbkz=1  " +
"and ((b.PFFZR_ID=-1 and SQJE>0) or (PFFZR_ID<>-1))   and (a.zt=0 or a.zt=1)  group by zbid)c on a.ZBID=c.zbid " +
"left join " +
"(select ZBID, Sum(isnull(je, 0))JE from zb_view_zlzml Where  (zlzLB = '0' or zlzLB = '1') and zbkzbz = '1' and zt = 0 group by zbid)d on a.ZBID = d.zbid " +
"left join " +
"(select zbid,sum(bcsyje)je from CG_CGXMZB group by zbid)e on a.zbid=e.zbid";
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }

            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "zbid";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        //查询指标列表
        public static async Task<DataTable> GetCGZBbyids( string []listids)
        {
            string listid = "";
            for (int i = 0; i < listids.Length; i++)
            {
                listid += "'" + listids[i].ToString() + "',";

            }
            listid = listid.Substring(0, listid.LastIndexOf(","));
            string sql = @"select a.zbid,''zbzy,a.zy,isnull(a.je,0)zbje,(isnull(d.je,0)+isnull(SQJHJE,0)+isnull(PFJHJE,0)+isnull(e.je,0))yyje,isnull(a.je,0)-isnull(d.je,0)-isnull(SQJHJE,0)-isnull(PFJHJE,0)-isnull(e.je,0) kyje,''zcsx,ysdwdm+':'+ysdwmc dw,xmdm+':'+xmmc xm,zclxdm+':'+zclxmc jflx,zblydm+':'+zblymc zbly,zjxzdm+':'+zjxzmc zjly,yskmdm+':'+yskmmc gnkm,zfjflxdm+':'+zfjflxmc jjfl from 

(select * from ZB_view_MXZB where zbid in(" + listid + ") and ZBZT<>'2'   and MXZBLB='0'   and shr_id<>-1)a " +
"left join " +
"(select ZBID,sum(case when (b.PFFZR_ID=-1 ) then SQJE else 0 end) SQJHJE,sum(case when (b.PFFZR_ID<>-1) then JE else 0 end) PFJHJE from ZB_YKJHML  a, ZB_YKJHNR  b  where a.gsdm=b.gsdm  " +
"and a.kjnd=b.kjnd    and a.jhbh=b.jhbh    and a.jhlb=b.jhlb    and a.sfzbkz=1  " +
"and ((b.PFFZR_ID=-1 and SQJE>0) or (PFFZR_ID<>-1))   and (a.zt=0 or a.zt=1)  group by zbid)c on a.ZBID=c.zbid " +
"left join " +
"(select ZBID, Sum(isnull(je, 0))JE from zb_view_zlzml Where  (zlzLB = '0' or zlzLB = '1') and zbkzbz = '1' and zt = 0 group by zbid)d on a.ZBID = d.zbid " +
"left join " +
"(select zbid,sum(bcsyje)je from CG_CGXMZB group by zbid)e on a.zbid=e.zbid";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<string> InsertCGXMML(CG_CGXMML xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
            return xm.listid;
        }
        public static async Task InsertCGXMNR(CG_CGXMMX xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task InsertCGXM_JH(CG_CGXM_JH xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task InsertCGXM_ZB(CG_CGXMZB xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }

        public static async Task UpdateCGXMML(CG_CGXMML xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task UpdateCGXMNR(CG_CGXMMX xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task UpdateCGXM_JH(CG_CGXM_JH xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task UpdateCGXM_ZB(CG_CGXMZB xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }

        public static async Task DeleteCGXMML(String listid)
        {
            string sql = "delete from CG_CGXMML where listid='" + listid + "';";
            sql += "delete from CG_CGXMMX where xmid='" + listid+"';";
            sql += "delete from CG_CGXMZB where xmid='" + listid + "';";
            sql += "delete from CG_CGXM_JH where xmid='" + listid + "';";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task DeleteCGXMNR(string cgmx,String listid)
        {
            string sql = "delete from CG_CGXMMX where xmid='" + listid + "' and listid not in" + cgmx + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task DeleteCGXM_JH(string cgjh,String listid)
        {
            string sql = "delete from CG_CGXM_JH where xmid='" + listid + "' and listid not in" + cgjh + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task DeleteCGXM_ZB(string cgzb,String listid)
        {
            string sql = "delete from CG_CGXMZB where xmid='" + listid + "' and listid not in"+ cgzb+";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        //提交采购项目
        public static async Task SubmitCGXM(string listid)
        {
            string sql = @"update CG_CGXMML set zt='1' where  listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        #endregion
        #region 采购标段
        //根据查询条件反馈列表
        public static async Task<(IEnumerable<dynamic>, int)> GetBDXX(CGBDQuerry querry,string strdepids)
        {
            string str = "";
            //查询条件--标段代码
            if (!(string.IsNullOrEmpty(querry.bdmc)))
            {
                str += " and bdmc like '%" + querry.bdmc + "%'";
            }
            //查询条件--部门
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--采购编号
            if (!(string.IsNullOrEmpty(querry.cgbh)))
            {
                str += " and cgbh ike '%" + querry.cgbh + "%'";
            }
            //查询条件--状态
            if (!(string.IsNullOrEmpty(querry.zt)))
            {
                str += " and zt in('" + querry.zt + "')";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            //查询条件--查询开始时间
            if (querry.Begindate != null)
            {
                str += " and lrrq >= '" + querry.Begindate + "'";
            }
            //查询条件--查询结束时间
            if (querry.Enddate != null)
            {
                str += " and lrrq <= '" + querry.Enddate + "'";
            }
            string sql = "select * from CG_BDXXML where bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
       
        //查询采购项目列表
        public static async Task<(DataTable, int)> GetCGXMMXLB(string listid,int Size, int Current, string sortString, string sort, string strdepids)
        {
            string sql = @"select a.listid cgmxid,cgpm,wpmc,ggxh,jldw,xmsl,xmwpdj,xmje,bz,isnull(yysl,0)yysl,isnull(a.xmsl,0)-isnull(yysl,0) kysl,isnull(yyje,0)yyje,isnull(xmje,0)-isnull(yyje,0) kyje from cg_cgxmmx a 
left join 
(select cgmxid,sum(bdsl)yysl,sum(bdje)yyje from  CG_BDXXMX group by cgmxid) b on a.listid=b.cgmxid where xmid='" + listid + "'";
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }

            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "cgmxid";
            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        public static async Task<DataTable> GetCGXMMXbyids(string []listids)
        {
            string listid = "";
            for (int i = 0; i < listids.Length; i++)
            {
                listid += "'" + listids[i].ToString() + "',";

            }
            listid = listid.Substring(0, listid.LastIndexOf(","));
            string sql = @"select a.listid cgmxid,cgpm,wpmc,ggxh,jldw,xmsl,xmwpdj,xmje,bz,isnull(yysl,0)yysl,isnull(a.xmsl,0)-isnull(yysl,0) kysl,isnull(yyje,0)yyje,isnull(xmje,0)-isnull(yyje,0) kyje from cg_cgxmmx a 
left join 
(select cgmxid,sum(bdsl)yysl,sum(bdje)yyje from  CG_BDXXMX group by cgmxid) b on a.listid=b.cgmxid where listid in(" + listid + ")";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<DataTable> GetCGXMbyids(string listids)
        {
            string sql = @"select a.listid xmid, kjnd, bmdm, bmmc, xmdm, xmmc, xmqdrq, cgfs, cgly, lxr, lxdh, zjjg, zjly, je xmje, zjlyfs, zzfs, bz, lrrid, lrr,lrrq, zt, isnull(yyje, 0)yyje,isnull(je, 0) - isnull(yyje, 0) kyje from
cg_cgxmml a
left join
(select xmid, sum(je) yyje from CG_BDXXML group by xmid)c on c.xmid = a.listid where listid in(" + listids + ")";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询采购项目列表
        public static async Task<(DataTable, int)> GetCGXMLB(string kjnd, int Size, int Current, string sortString, string sort, string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            string sql = @"select a.listid xmid, kjnd, bmdm, bmmc, xmdm, xmmc, xmqdrq, cgfs, cgly, lxr, lxdh, zjjg, zjly, je xmje, zjlyfs, zzfs, bz, lrrid, lrr, 
lrrq, zt,isnull(yyje,0)yyje,isnull(je,0)-isnull(yyje,0) kyje from 
cg_cgxmml a
left join
(select xmid, sum(je) yyje from CG_BDXXML group by xmid)c on c.xmid =a.listid where bmdm in " + strdepids + "" + str;
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }

            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "xmid";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        public static async Task InsertBDML(CG_BDXXML xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateBDML(CG_BDXXML xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeleteBDML(string listid)
        {
            string sql = "delete from CG_BDXXML where listid=" + listid + "";
            sql += " delete from CG_BDXXMX where bdid=" + listid + "";

            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task InsertBDMX(CG_BDXXMX xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateBDMX(CG_BDXXMX xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeleteBDNR(string bdmx, String listid)
        {
            string sql = "delete from CG_BDXXMX where bdid='" + listid + "' and listid not in" + bdmx + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        //查询单个采购项目标段
        public static async Task<DataTable> GetCGBDMLByID(string listid)
        {
            string sql = @"select a.*,isnull(yyje,0)yyje,isnull(xmje,0)-isnull(yyje,0) kyje from CG_BDXXML a 
left join cg_cgxmml b on xmid = b.listid
left join
(select xmid, sum(je) yyje from CG_BDXXML where listid!='" + listid + "' group by xmid)c on c.xmid = b.listid where a.listid = '" + listid +"' order by listid; ";  //明细
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个采购项目标段的明细
        public static async Task<DataTable> GetCGBDMXByID(string listid)
        {
            string sql = @"select a.*,isnull(yysl,0)yysl,case isnull(b.xmsl,0) when 1 then 1 else isnull(b.xmsl,0)-isnull(yysl,0) end kysl,isnull(yyje,0)yyje,isnull(xmje,0)-isnull(yyje,0) kyje from CG_BDXXMX a 
left join
cg_cgxmmx b on a.cgmxid = b.listid
left join
(select cgmxid, sum(bdsl) yysl, sum(bdje) yyje from CG_BDXXMX where cgmxid!='" + listid + "' group by cgmxid)c on c.cgmxid = b.listid where a.bdid=" + listid + " ";  //明细
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //提交采购项目
        public static async Task SubmitBDML(string listid)
        {
            string sql = @"update CG_BDXXML set zt='1' where  listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        #endregion
        #region 招标信息
        //根据查询条件反馈列表
        public static async Task<(IEnumerable<dynamic>,int)> GetZBXX(ZBXXQuerry querry,string strdepids)
        {
            string str = "";
            //查询条件--采购项目名称
            if (!(string.IsNullOrEmpty(querry.bdmc)))
            {
                str += " and bdmc like '%" + querry.bdmc + "%'";
            }
            //查询条件--采购方式
            if (!(string.IsNullOrEmpty(querry.cgfs)))
            {
                str += " and cgfs in('" + querry.cgfs + "')";
            }
            //查询条件--部门
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--查询开始时间
            if (querry.Begindate != null)
            {
                str += " and lrrq >= '" + querry.Begindate + "'";
            }
            //查询条件--查询结束时间
            if (querry.Enddate != null)
            {
                str += " and lrrq <= '" + querry.Enddate + "'";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            string sql = "select * from View_ZBXX where  bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
        //获取标段列表
        public static async Task<(DataTable, int)> GetBDLB(string kjnd, int Size, int Current, string sortString, string sort, string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            string sql = "select listid bdid, kjnd, bmdm, bmmc, xmmc, cgfs, cgbh, xmje, bddm, bdmc, bdwb, je bdje, bz, lrrid, lrr, lrrq, zt from CG_BDXXML where bmdm in " + strdepids + str;
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }

            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "bdid";
            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        //获取标段列表
        public static async Task<DataTable> GetBDbyids(string listid)
        {

            string sql = "select listid bdid, kjnd, bmdm, bmmc, xmmc, cgfs, cgbh, xmje, bddm, bdmc, bdwb, je bdje, bz, lrrid, lrr, lrrq, zt from CG_BDXXML where listid in (" + listid + ")";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个招投标
        public static async Task<DataTable> GetZBByID(string listid)
        {
            string sql = @"select a.*,b.cgfs, b.bddm, b.bdwb, b.bz from CG_ZBXXML a left join CG_BDXXML b on a.bdid=b.listid
where  a.listid='" + listid + "';";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个招投标
        public static async Task<DataTable> GetZBDWByID(string listid)
        {
            string sql = @"select * from  CG_ZBDWMX  where  zbid='" + listid + "';";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task InsertZBXXML(CG_ZBXXML xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateZBXXML(CG_ZBXXML xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeleteZBXXML(string listid)
        {
            string sql = "delete from CG_ZBXXML where listid=" + listid + "; ";
            sql += "delete from CG_ZBDWMX where zbid=" + listid + ";";

            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task SubmitZBXXML(string listid)
        {
            string sql = "update CG_ZBXXML set zt=1 where listid=" + listid + "; ";
          
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task InsertZBDWMX(CG_ZBDWMX xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateZBDWMX(CG_ZBDWMX xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeleteZBXMNR(string dwmx, string listid)
        {
            string sql = "delete from CG_ZBDWMX where zbid='" + listid + "' and listid not in" + dwmx + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        //查询单个采购项目标段的明细
        public static async Task<DataTable> GetCGBDMXBybdid(string listid)
        {
            string sql = @"select * from CG_BDXXMX where bdid=" + listid + " ";  //明细
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个采购项目标段的明细
        public static async Task<DataTable> GetCGBDMXByzbid(string listid)
        {
            string sql = @"select * from CG_BDXXMX where bdid=(select bdid from CG_ZBXXML where listid=" + listid + ") ";  //明细
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        #endregion
        #region 中标信息
        public static async Task<(IEnumerable<dynamic>,int)> GetPBZBXX(PBZBQuerry querry,string strdepids)
        {
            string str = "";
            //查询条件--采购项目名称
            if (!(string.IsNullOrEmpty(querry.cgbh)))
            {
                str += " and cgbh like '%" + querry.cgbh + "%'";
            }
            //查询条件--采购单位
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--状态
            if (!(string.IsNullOrEmpty(querry.zt)))
            {
                str += " and zt in('" + querry.zt + "')";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            //查询条件--查询开始时间
            if (querry.Begindate != null)
            {
                str += " and lrrq >= '" + querry.Begindate + "'";
            }
            //查询条件--查询结束时间
            if (querry.Enddate != null)
            {
                str += " and lrrq <= '" + querry.Enddate + "'";
            }
            string sql = "select * from View_PBZBXX where   bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
        //查询单个评标中标
        public static async Task<DataTable> GetPBZBByID(string listid)
        {
            string sql = @"select a.*,xmdm,kbrq,bddm from CG_PBZBML a left join View_ZBXX b on a.zbid=b.listid where  a.listid='" + listid + "'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询招投标列表
        public static async Task<(DataTable,int)> GetZBXXLB(string kjnd, int Size, int Current, string sortString, string sort,string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            string sql = @"select  listid zbid, bmdm, bmmc, bddm, bdmc, bdje, cgbh, zbfs,zbfzr, ggnr, lrrid, lrr, lrrq, zt, cgfs,kbrq,zbfsmc
                           from View_ZBXX where  bmdm in " + strdepids + " "+str;
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }

            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "zbid";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);

        }
        //查询招投标列表
        public static async Task<DataTable> GetZBXXbyid(string listid)
        {
          
            string sql = @"select  listid zbid, bmdm, bmmc, bddm, bdmc, bdje, cgbh, zbfs,zbfzr, ggnr, lrrid, lrr, lrrq, zt, cgfs,kbrq,zbfsmc
                            from View_ZBXX where  listid='" + listid + "'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询招投标列表
        public static async Task<DataTable> GetZBXXbypbzbid(string listid)
        {

            string sql = @"select  listid zbid, bmdm, bmmc, bddm, bdmc, bdje, cgbh, zbfs,zbfzr, ggnr, lrrid, lrr, lrrq, zt, cgfs,kbrq,zbfsmc
                            from View_ZBXX where  listid=(select zbid from cg_pbzbml where listid='" + listid + "')";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task InsertPBZB(CG_PBZBML xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdatePBZB(CG_PBZBML xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeletePBZB(string listid)
        {
            string sql = @"delete from CG_PBZBML where listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task SubmitPBZB(string listid)
        {
            string sql = @"update CG_PBZBML set zt=1 where listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        #endregion
        #region 采购合同及变更
        public static async Task<(IEnumerable<dynamic>, int)> GetHTXX(HTXXQuerry querry,string strdepids)
        {
            string str = "";
            //查询条件--合同编号
            if (!(string.IsNullOrEmpty(querry.htbh)))
            {
                str += " and htbh like '%" + querry.htbh + "%'";
            }
            //查询条件--合同名称
            if (!(string.IsNullOrEmpty(querry.htmc)))
            {
                str += " and htmc like '%" + querry.htmc + "%'";
            }
            //查询条件--采购单位
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--状态
            if (!(string.IsNullOrEmpty(querry.zt)))
            {
                str += " and zt in('" + querry.zt + "')";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            //查询条件--查询开始时间
            if (querry.Begindate != null)
            {
                str += " and lrrq >= '" + querry.Begindate + "'";
            }
            //查询条件--查询结束时间
            if (querry.Enddate != null)
            {
                str += " and lrrq <= '" + querry.Enddate + "'";
            }
            string sql = "select * from View_HTXX where bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
        //查询单个评标中标
        public static async Task<DataTable> GetHTByID(string listid)
        {
            string sql = @"select * from CG_HTXX where  listid='" + listid + "';";
           
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个评标中标
        public static async Task<DataTable> GetHTSKZHByID(string listid)
        {
            string sql = @"select * from CG_HTSKZH where  htid='" + listid + "' order by listid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个评标中标
        public static async Task<DataTable> GetHTFKJHByID(string listid)
        {
            string sql = @"select * from CG_HTFKJH where  htid='" + listid + "' order by listid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个评标中标
        public static async Task<DataTable> GetHTBZJByID(string listid)
        {
            string sql = @"select * from CG_HTBZJ where  htid='" + listid + "' order by listid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        //查询单个评标中标
        public static async Task<DataTable> GetHTBDXXByID(string listid)
        {
            string sql = @"select * from CG_BDXXMX where bdid=(select bdid from CG_ZBXXML where listid=(select zbid from CG_PBZBML where listid=(select pbzbid from CG_HTXX where listid=" + listid + "))) order by listid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<(DataTable, int)> GetPBZBLB(string kjnd, int Size, int Current, string sortString, string sort, string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            string sql = @"select listid pbzbid, kjnd, bdmc, bdje, bmdm, bmmc, pbdw, pbsj, pbdd, pbfzr, pw, zbdw, zbsj, zbdz, zbje, lxfs, lrrid, lrr, lrrq, 
                zt, zbid, cgfs, kbrq from View_PBZBXX where  bmdm in " +  strdepids +"" +str;
            bool isAsc = true;
            if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }

            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "pbzbid";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        public static async Task<DataTable> GetBDXXByPBID(string listid)
        {
            string sql = @"select * from CG_BDXXMX where bdid=(select bdid from CG_ZBXXML where listid=(select zbid from CG_PBZBML where listid=" + listid + ")) order by listid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }

        public static async Task DeleteHTSKZH(string cgmx, String listid)
        {
            string sql = "delete from CG_HTSKZH where htid='" + listid + "' and listid not in" + cgmx + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task DeleteHTFKJH(string cgjh, String listid)
        {
            string sql = "delete from CG_HTFKJH where htid='" + listid + "' and listid not in" + cgjh + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task DeleteHTBZJ(string cgzb, String listid)
        {
            string sql = "delete from CG_HTBZJ where htid='" + listid + "' and listid not in" + cgzb + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task InsertHTXX(CG_HTXX xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateHTXX(CG_HTXX xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeleteHTXX(string listid)
        {
            string sql = @"delete from CG_HTXX where  listid='" + listid + "';";
            sql += @"delete from CG_HTSKZH where  htid='" + listid + "';";
            sql += @"delete from CG_HTFKJH where  htid='" + listid + "';";
            sql += @"delete from CG_HTBZJ where  htid='" + listid + "';";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task SubmitHTXX(string listid)
        {
            string sql = @"update CG_HTXX set zt=1 where listid='" + listid;
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task InsertSKZH(CG_HTSKZH xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateSKZH(CG_HTSKZH xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }

        public static async Task InsertFKJH(CG_HTFKJH xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateFKJH(CG_HTFKJH xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
       
        public static async Task InsertBZJ(CG_HTBZJ xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateBZJ(CG_HTBZJ xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }

        #endregion
        #region 项目验收
        public static async Task<(IEnumerable<dynamic>, int)> GetXMYSXX(XMYSQuerry querry,string strdepids)
        {
            string str = "";
           
            //查询条件--采购单位
            if (!(string.IsNullOrEmpty(querry.bmdm)))
            {
                str += " and bmdm in('" + querry.bmdm + "')";
            }
            //查询条件--状态
            if (!(string.IsNullOrEmpty(querry.zt)))
            {
                str += " and zt in('" + querry.zt + "')";
            }
            //查询条件--会计年度
            if (!(string.IsNullOrEmpty(querry.kjnd)))
            {
                str += " and kjnd in('" + querry.kjnd + "')";
            }
            //查询条件--查询开始时间
            if (querry.Begindate != null)
            {
                str += " and lrrq >= '" + querry.Begindate + "'";
            }
            //查询条件--查询结束时间
            if (querry.Enddate != null)
            {
                str += " and lrrq <= '" + querry.Enddate + "'";
            }
            string sql = "select * from CG_XMYSML where   bmdm in " + strdepids + str;
            bool isAsc = true;
            if (querry.Sort.ToUpper() != "ASC")
            {
                isAsc = false;
            }
            return await new RepositoryFactory().BaseRepository().FindList(sql, null, querry.SortString, isAsc, querry.Size, querry.Current);
        }
        //查询采购项目列表
        public static async Task<(DataTable, int)> GetYSBDLB(string kjnd, int Size, int Current, string sortString, string sort, string strdepids)
        {
            string str = "";
            if (string.IsNullOrEmpty(kjnd))
            {
            }
            else
            {
                if (kjnd != "")
                {
                    str += " and kjnd in('" + kjnd + "')";
                }
            }
            string sql = @"select listid bdid, bmdm, bmmc, xmmc, cgfs, cgbh, xmje, bddm, bdmc, bdwb, je bdje, bz, zt,isnull(yysje,0)yysje,isnull(je,0)-isnull(yysje,0) dysje from cg_bdxxml  a
left join 
(select bdid,isnull(sum(ysje),0)yysje from CG_XMYSML where 1=1" + str + " group by bdid)b on a.listid=b.bdid where a.bmdm in " + strdepids + "" + str;
            bool isAsc = true;
             if (string.IsNullOrEmpty(sort))
            {
                isAsc = false;
            }
            else
            {
                if (sort.ToUpper() != "ASC")
                {
                    isAsc = false;
                }
                    
            }
            if (Size == 0)
            {
                Size = 20;
            }
            if (Current == 0)
            {
                Current = 1;
            }
            if (string.IsNullOrEmpty(sortString))
            {
                sortString = "bdid";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, sortString, isAsc, Size, Current);
        }
        public static async Task<DataTable> GetYSBDBybdid(string listid)
        {

            string sql = @"select listid bdid, bmdm, bmmc, xmmc, cgfs, cgbh, xmje, bddm, bdmc, bdwb, je bdje, bz, zt,isnull(yysje,0)yysje,isnull(je,0)-isnull(yysje,0) dysje from cg_bdxxml  a
left join 
(select bdid,isnull(sum(ysje),0)yysje from CG_XMYSML  group by bdid)b on a.listid=b.bdid where listid='" + listid + "'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<DataTable> GetYSBDMXBybdid(string listid)
        {
            string sql = @"SELECT listid bdmxid, bdid, cgpm, wpmc, ggxh, jldw, cs, cd, pp, ppmx, bdsl, bddj, bdje, bz, cgmxid,isnull(yyssl,0)yyssl,case bdsl when 1 then 1 else isnull(bdsl,0)-isnull(yyssl,0) end dyssl,isnull(yysje,0)yysje,isnull(bdje,0)-isnull(yysje,0) dysje FROM  CG_BDXXMX a 
left join (select bdmxid,isnull(sum(yssl),0)yyssl,isnull(sum(ysje),0)yysje from CG_XMYSMX group by bdmxid)b on listid=bdmxid where bdid='" + listid + "' order by listid";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task InsertYSXXML(CG_XMYSML xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateYSXXML(CG_XMYSML xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task InsertYSMX(CG_XMYSMX xm)
        {
            await new RepositoryFactory().BaseRepository().Insert(xm);
        }
        public static async Task UpdateYSMX(CG_XMYSMX xm)
        {
            await new RepositoryFactory().BaseRepository().Update(xm);
        }
        public static async Task DeleteYSMX(string ysmx, String listid)
        {
            string sql = "delete from CG_XMYSMX where ysid='" + listid + "' and listid not in" + ysmx + ";";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task<DataTable> GetYSMLByYSid(string listid)
        {
            string sql = @"select a.listid, a.kjnd, a.bmdm, a.bmmc, a.bdmc, ysrq, bdje, ysmc, ysje, ysr, a.bz, a.zt,a.lrrid,a.lrr,a.lrrq, a.bdid,isnull(xmje,0)xmje,isnull(yysje,0)yysje,isnull(je,0)-isnull(yysje,0) dysje from CG_XMYSML a
left join
cg_bdxxml  b on a.bdid=b.listid
left join 
(select bdid,isnull(sum(ysje),0)yysje from CG_XMYSML where listid!='" + listid + "'  group by bdid)c on b.listid=c.bdid where a.listid='" + listid+"';";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<DataTable> GetYSMXByYSid(string listid)
        {
            string sql = @"SELECT   a.listid, ysid, a.cgpm, a.wpmc, a.ggxh, a.jldw, a.cs, a.cd, a.pp, a.ppmx, yssl, ysdj, ysje, a.bz, a.bdmxid,isnull(yyssl,0)yyssl,case bdsl when 1 then 1 else isnull(bdsl,0)-isnull(yyssl,0) end dyssl,isnull(yysje,0)yysje,isnull(bdje,0)-isnull(yysje,0) dysje FROM      
CG_XMYSMX a
left join 
CG_BDXXMX b on a.bdmxid=b.listid
left join (select bdmxid,isnull(sum(yssl),0)yyssl,isnull(sum(ysje),0)yysje from CG_XMYSMX where ysid!='" + listid + "' group by bdmxid)c on b.listid=c.bdmxid where ysid='" + listid + "' order by listid;";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task<DataTable> GetYSBDByYSid(string listid)
        {

            string sql = @"select listid bdid, bmdm, bmmc, xmmc, cgfs, cgbh, xmje, bddm, bdmc, bdwb, je bdje, bz, zt,isnull(yysje,0)yysje,isnull(je,0)-isnull(yysje,0) dysje from cg_bdxxml  a
left join 
(select bdid,isnull(sum(ysje),0)yysje from CG_XMYSML where listid='" + listid + "' group by bdid)b on a.listid=b.bdid where listid=(select bdid from CG_XMYSML where listid='" + listid + "')";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }
        public static async Task DeleteYSXX(string listid)
        {
            string sql = @"delete from CG_XMYSML where  listid='" + listid + "';";
            sql += @"delete from CG_XMYSMX where  ysid='" + listid + "';";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        public static async Task SubmitYSXX(string listid)
        {
            string sql = @"update CG_XMYSML set zt=1 where listid='" + listid + "'";
            await new RepositoryFactory().BaseRepository().ExecuteSql(sql);
        }
        #endregion
    }
}
